﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;

namespace CardLogistics.DAL
{
     /// <summary>
    /// 功能说明：公用的删除和获取信息类
    /// </summary>
    public class CommonData
    {
        #region 删除、编辑信息
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="StrWhere">删除条件</param>
        /// <param name="StrTable">要删除的表名</param>
        /// <param name="StrUpdateSetString">要更新的字段信息</param>
        /// <param name="IntFlag">1 物理删除，0 逻辑删除</param>
        /// <returns>返回影响的行数</returns>
        public static bool Delete(string StrWhere, string StrTable, string StrUpdateSetString, int IntFlag)
        {
            OracleParameter[] parameters = {
					new OracleParameter("StrWhere", OracleType.VarChar),
                    new OracleParameter("TableName", OracleType.VarChar),
                    new OracleParameter("updateSetString", OracleType.VarChar),
                    new OracleParameter("Flag", OracleType.Number) };
            parameters[0].Value = StrWhere;
            parameters[1].Value = StrTable;
            parameters[2].Value = StrUpdateSetString;
            parameters[3].Value = IntFlag;
            int objValue = DbHelperSQL.ExecuteNonQuery(CommandType.StoredProcedure, "P_Public_Delete", parameters);
            if (objValue > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 根据条件查询相应的表的字段,添加排序条件
        /// <summary>
        /// 根据条件查询相应的表的字段,添加排序条件
        /// </summary>
        /// <param name="StrWhere">查询条件</param>
        /// <param name="StrTableName">表名或者试图名称</param>
        /// <param name="StrFields">要查询的字段，*为所有字段</param>
        /// <param name="StrOrderField">要排序的字段(为空是不进行排序)</param>
        /// <param name="InOrderType">排序方式，0：倒序,1:顺序</param>
        /// <returns>填充完数据的DataTable</returns>
        public static DataTable GetListOrderBy(string StrWhere, string StrTableName, string StrFields, string StrOrderField, int InOrderType)
        {
            OracleParameter[] parameters = {
					new OracleParameter("StrWhere", OracleType.VarChar),
                    new OracleParameter("TableName", OracleType.VarChar),
                    new OracleParameter("Field", OracleType.VarChar),
                    new OracleParameter("OrderField", OracleType.VarChar),
                    new OracleParameter("OrderType", OracleType.VarChar),
                    new OracleParameter("curArg", OracleType.Cursor)};
            if (StrWhere == "")
            {
                parameters[0].Value = " 1=1 ";
            }
            else
            {
                parameters[0].Value = StrWhere;
            }
            parameters[1].Value = StrTableName;
            parameters[2].Value = StrFields;
            parameters[3].Value = StrOrderField;
            if (InOrderType == 0)
            {
                parameters[4].Value = "desc";
            }
            else
            {
                parameters[4].Value = "asc";
            }
            parameters[5].Direction = ParameterDirection.Output;
            return DbHelperSQL.FillDataTable(CommandType.StoredProcedure, "P_Pub_GetListOrderBy", parameters);
        }
        #endregion

        #region 取得分页数据
         /// <summary>
        /// Oracle存储过程分页
        /// </summary>
        /// <param name="tName">表名</param>
        /// <param name="keyId">主键字段</param>
        /// <param name="sortRow">排序的列及排序方式</param>
        /// <param name="pageSize">没有显示的条数</param>
        /// <param name="curPage">当前页</param>
        /// <param name="strSql">查询条件</param>
        /// <param name="shownClm">显示的列</param>
        /// <param name="pageCount">返回总页数</param>
        /// <param name="RecordCount">返回总记录数</param>
        /// <returns></returns>
        public static DataTable QuickPage_Oracle(string tName, string keyId, string sortRow, int pageSize, int curPage, string strSql, string shownClm, out int pageCount, out int RecordCount)
        {
            OracleParameter[] parameters ={
                     new OracleParameter("TABLES",OracleType.VarChar),
                     new OracleParameter("PK",OracleType.VarChar),
                     new OracleParameter("SORT",OracleType.VarChar),
                     new OracleParameter("PAGENUMBER",OracleType.Number),
                     new OracleParameter("PAGESIZE",OracleType.Number),
                     new OracleParameter("FILTER",OracleType.VarChar),
                     new OracleParameter("FIELDS",OracleType.VarChar),
                     new OracleParameter("v_cur",OracleType.Cursor), 
                     new OracleParameter("ISCOUNT",OracleType.Number),
                     new OracleParameter("counts",OracleType.Number)};
            parameters[0].Value = tName;  // 表或视图名称
            parameters[1].Value = keyId;         // 主键
            parameters[2].Value = sortRow;       // 排序字段及排序方式
            parameters[3].Value = curPage; // 索引页
            parameters[4].Value = pageSize;   // 多少条为一页
            parameters[5].Value = strSql;     // 查询条件组合
            parameters[6].Value = shownClm;     // 查询的字段
            parameters[7].Direction = ParameterDirection.Output;//输出
            parameters[8].Value = 0;
            parameters[9].Direction = ParameterDirection.Output;    //输出总行数
            DataSet ds = DbHelperSQL.RunProcedureByPager("p_page.ASPNETPAGE", parameters);
            RecordCount = int.Parse(parameters[9].Value.ToString());
            if (RecordCount % pageSize == 0)
            {
                pageCount = RecordCount / pageSize;
            }
            else
                pageCount = RecordCount / pageSize + 1;
            return ds.Tables[0];
        }
        #endregion

        #region 获取系统时间
        public static DateTime GetSysDateTime()
        {
            string StrSql = "SELECT sysdate from dual";
            return Convert.ToDateTime(DbHelperSQL.ExecuteScalar(CommandType.Text, StrSql, null).ToString());
        }
        #endregion

        #region  执行Sql函数查询
        /// <summary>
        /// 执行Sql函数查询
        /// </summary>
        /// <param name="pStrFuncName">函数名称</param>
        /// <param name="pStrParameter">参数</param>
        /// <returns></returns>
        public static string GetSqlFunctionReturnValue(string pStrFuncName, string pStrParameter)
        {
            string pStrSql = "SELECT " + pStrFuncName + "(" + pStrParameter + ")";
            DataTable pDataTable = DbHelperSQL.GetQueryResult(pStrSql);
            if (pDataTable != null)
            {
                if (pDataTable.Rows.Count != 0)
                    return pDataTable.Rows[0][0].ToString();
                else
                    return "";
            }
            else
                return "";
        }
        #endregion

        #region 获取一条，多条，符合条件的记录集
        /// <summary>
        /// 获取一条，多条，符合条件的记录集
        /// </summary>
        /// <param name="pStrField">字段名</param>
        /// <param name="pStrTable">表名</param>
        /// <param name="pStrWhere">条件</param>
        /// <returns>记录集(DataTable)</returns>
        public static DataTable GetList(string pStrField, string pStrTable, string pStrWhere)
        {
            OracleParameter[] parameters = {
                                            new OracleParameter("StrWhere", OracleType.VarChar),
                                            new OracleParameter("TableName", OracleType.VarChar),
                                            new OracleParameter("Field", OracleType.VarChar),
                                            new OracleParameter("curArg", OracleType.Cursor)
                                         };
            parameters[0].Value = pStrWhere;
            parameters[1].Value = pStrTable;
            parameters[2].Value = pStrField;
            parameters[3].Direction = ParameterDirection.Output;
            return DbHelperSQL.FillDataTable(CommandType.StoredProcedure, "P_Pub_GetList", parameters);
        }
        #endregion
    }
}
